package cn.edu.klmy.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Repository;

import web.util.DaoUtil;
import cn.edu.klmy.dao.ICourseStructureDao;
import cn.edu.klmy.models.course.CourseStructure;

@Repository
public class CourseStructureDao extends JdbcDaoSupport implements ICourseStructureDao {

    private static final String ADD_COURSESTRUCTURE = "INSERT INTO COURSE_STRUCTURE(STUDY_AREA, STUDY_SUBJECT, STUDY_TYPE) VALUES(?, ?, ?)";
    @Override
    public void addCourseStructure(CourseStructure courseStructure) {
        getJdbcTemplate().update(ADD_COURSESTRUCTURE, new Object[]{courseStructure.getStudyArea(), courseStructure.getStudySubject(), courseStructure.getStudyType()});
    }

    private static final String UPDATE_COURSESTRUCTURE = "UPDATE COURSE_STRUCTURE SET STUDY_AREA = ?, STUDY_SUBJECT = ?, STUDY_TYPE = ? WHERE COURSE_STRUCTURE_ID = ?";

    @Override
    public void updateCourseStructure(CourseStructure courseStructure) {
        getJdbcTemplate().update(UPDATE_COURSESTRUCTURE, new Object[]{courseStructure.getStudyArea(), courseStructure.getStudySubject(), courseStructure.getStudyType(), courseStructure.getId()});
    }

    private static final String REMOVE_COURSESTRUCTURE = "DELETE FROM COURSE_STRUCTURE WHERE ";


    private static final String REMOVE_COURSE = "DELETE FROM COURSE WHERE COURSE_STRUCTURE_ID IN (SELECT COURSE_STRUCTURE_ID FROM COURSE_STRUCTURE WHERE ";
    private static final String REMOVE_TEXTBOOK = "DELETE FROM TEXTBOOK WHERE COURSE_ID IN (SELECT ID FROM COURSE WHERE COURSE_STRUCTURE_ID IN (SELECT COURSE_STRUCTURE_ID FROM COURSE_STRUCTURE WHERE ";

    @Override
    public void removeCourseStructure(String fieldName, Object value) {
    	String sql1 = REMOVE_COURSESTRUCTURE + fieldName + "=" + value;
        String sql2 = REMOVE_COURSE + fieldName + "=" + value + ")";
        String sql3 = REMOVE_TEXTBOOK + fieldName + "=" + value + "))";
        getJdbcTemplate().update(sql3);
        getJdbcTemplate().update(sql2);
        getJdbcTemplate().update(sql1);
    }

    private static final String GET_COURSESTRUCTURES = "SELECT * FROM COURSE_STRUCTURE WHERE COURSE_STRUCTURE_ID = ?";

    @Override
    public CourseStructure getCourseStructure(int courseStructureId) {
        List<CourseStructure> courseStructures = (List<CourseStructure>) getJdbcTemplate().query(GET_COURSESTRUCTURES, new Object[]{courseStructureId}, new CourseStructureMapper());
        if (null != courseStructures && !courseStructures.isEmpty())
            return courseStructures.get(0);
        return null;
    }

    @Override
    public List<CourseStructure> getCourseStructures(Map<String, Object[]> filter, int pageNum, int pageSize) {
        String sql = DaoUtil.getFuzzyQueryPageSql("COURSE_STRUCTURE", filter, pageNum, pageSize);
        return (List<CourseStructure>) getJdbcTemplate().query(sql, new Object[]{}, new CourseStructureMapper());
    }

    @Override
    public int getCourseStructureCount(Map<String, Object[]> filter) {
        String sql = DaoUtil.getFuzzyQueryCountSql("COURSE_STRUCTURE", filter);
        return getJdbcTemplate().queryForInt(sql);
    }

    @Resource(name = "dataSource")
    public void setSuperDataSource(DataSource dataSource) {
        super.setDataSource(dataSource);
    }

    private static class CourseStructureMapper implements RowMapper<CourseStructure> {

        @Override
        public CourseStructure mapRow(ResultSet resultSet, int i) throws SQLException {
            CourseStructure courseStructure = new CourseStructure();
            courseStructure.setId(resultSet.getInt("COURSE_STRUCTURE_ID"));
            courseStructure.setStudyArea(resultSet.getString("STUDY_AREA"));
            courseStructure.setStudySubject(resultSet.getString("STUDY_SUBJECT"));
            courseStructure.setStudyType(resultSet.getInt("STUDY_TYPE"));
            return courseStructure;
        }
    }

    private static final String COURSESTRUCTURES_EXISTS = "SELECT COUNT(*) FROM COURSE_STRUCTURE WHERE STUDY_AREA = ? AND STUDY_SUBJECT = ?";

	@Override
	public boolean courseStructureExists(String studyArea, String studySubject) {
        int result = getJdbcTemplate().queryForInt(COURSESTRUCTURES_EXISTS, new Object[]{studyArea, studySubject});

        if (result < 1) {
            return false;
        }
        return true;
	}
}
